-- created by kumiler
-- on 2022/8/13 13:43
-- DESC 任务单错用明细表
drop table if exists jms_dm.dm_tms_waybill_wrong_shipment_dt;
create table if not exists jms_dm.dm_tms_waybill_wrong_shipment_dt
(
    date_time             date comment '日期',
    load_manage_code      varchar(64) comment '装车扫描所属管理大区编码',
    unload_manage_code    varchar(64) comment '卸车到件所属管理大区编码',
    load_agent_code       varchar(64) comment '装车扫描所属代理区编码',
    unload_agent_code     varchar(64) comment '卸车到件所属代理区编码',
    billcode              varchar(64) comment '运单号',
    ordersource_code      varchar(64) comment '订单来源编号',
    ordersource_name      varchar(64) comment '订单来源名称',
    goods_type_code       varchar(64) comment '物品类型编码',
    goods_type_name       varchar(64) comment '物品类型名称',
    load_manage_name      varchar(64) comment '装车扫描所属管理大区名称',
    load_agent_name       varchar(64) comment '装车扫描所属代理名称',
    load_scansitecode     varchar(64) comment '装车扫描编码',
    load_scansitename     varchar(64) comment '装车扫描名称',
    load_shipment_no      varchar(64) comment '装车扫描发车任务单号',
    load_shipment_name    varchar(64) comment '装车扫描发车任务号名称',
    loadship_start_code   varchar(64) comment '装车任务单起始站点',
    loadship_end_code     varchar(64) comment '装车任务单结束站点',
    load_wrong_shopment   tinyint comment '装车扫描发车是否任务单错用',
    load_scanuser_code    varchar(64) comment '装车扫描扫描员编码',
    load_scanuser_name    varchar(64) comment '装车扫描扫描员名称',
    load_pistolid         varchar(64) comment '装车扫描扫描设备编号',
    unload_manage_name    varchar(64) comment '卸车到件所属管理大区名称',
    unload_agent_name     varchar(64) comment '卸车到件所属代理名称',
    unload_center_code    varchar(64) comment '卸车到件所属中心编码',
    unload_center_name    varchar(64) comment '卸车到件所属中心名称',
    unload_shipment_no    varchar(64) comment '卸车到件任务单号',
    unload_shipment_name  varchar(64) comment '卸车到件任务名称',
    nuloadship_start_code varchar(64) comment '卸车到件起始网点',
    unloadship_end_code   varchar(64) comment '卸车到件结束网点',
    unload_wrong_shopment tinyint comment '卸车到件是否错用任务单',
    unload_scanuser_code  varchar(64) comment '卸车到件扫描员编码',
    unload_scanuser_name  varchar(64) comment '卸车到件扫描员名称',
    unload_pistolid       varchar(64) comment '卸车到件扫描设备编码',
    diff_shipment         tinyint comment '装卸任务单是否相同',
    wrong_shipment        tinyint comment '是否任务单错用'
) ENGINE = OLAP DUPLICATE KEY(date_time, load_manage_code, unload_manage_code)
COMMENT '车件分离卸车中心统计'
PARTITION BY RANGE(date_time)
(START ("2022-07-01") END ("2022-08-20") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(unload_manage_code) BUCKETS 10
PROPERTIES (
'replication_num' = '3',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-62',
'dynamic_partition.end' = '2',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '10',
'in_memory' = 'true',
'storage_format' = 'V2'
);
